- 脚本站点：https://launchpad.net/mysql-tuning-primer
- 下载URL：https://launchpadlibrarian.net/78745738/tuning-primer.sh
- 安装mariadb：
a. yum install mariadb mariadb-server - 启动并设置开机启动：
a. systemctl start mariadb
b. systemctl enable mariadb - 初始化mariadb：
a. mysql_secure_installation - 测试登陆：
a. mysql -uroot -pdell_456 - 将tuning-primer.sh拷贝到my.cnf的同级目录
- 运行：
[root@localhost etc]# sh tuning-primer.sh
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found potential sockets: /var/lib/mysql/mysql.sock //找到一个mysql.sock
Using: /var/lib/mysql/mysql.sock //使用这个.sock文件
Would you like to provide a different socket?: [y/N] N //是否使用其他的socket
Do you have your login handy ? [y/N] : y //是否手动输入用户名和密码
User: root
Password: dell_456
Would you like me to create a ~/.my.cnf file for you? [y/N] : N /是不是要帮你再创建一个my.cnf
- 运行结果：
– MYSQL PERFORMANCE TUNING PRIMER –- By: Matthew Montgomery -
MySQL Version 5.5.52-MariaDB x86_64
Uptime = 0 days 0 hrs 7 min 2 sec
Avg. qps = 0
Total Questions = 39
Threads Connected = 1
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations //告诉服务运行还不超过48个小时，这样不安全
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL’s Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 60 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
//告诉binary log没有设置生存时间，会一直保存下来，只到主服务器重起，
//并且binlog的同步并没有激活，如果服务器崩溃binlog数据将丢失
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See “MEMORY USAGE” section to make sure you are not over-allocating
//告诉目前已连接的最大连接和设置的最大连接的比率差不多是0％，
//建议我把最大连接设置小一点，可以节省一点内存
INNODB STATUS
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 274 M
Configured Max Per-thread Buffers : 419 M
Configured Max Global Buffers : 272 M
Configured Max Memory Limit : 691 M
Physical Memory : 3.68 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 99 K
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 2
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is supported but not enabled //查询缓存是支持的，但是没有激活
Perhaps you should set the query_cache_size //建议我设置一下query_cache_size的大小
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
No sort operations have been performed
Sort buffer seems to be fine
JOINS
tuning-primer.sh: line 402: export: `2097152’: not a valid identifier
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 400 tables
Current table_definition_cache = 400 tables
You have a total of 41 tables
You have 41 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 348 temp tables, 9% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 4 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 0 : 318
Your table locking seems to be fine
- 最后根据结果做相应的优化